﻿--
-----------------------------------------------------------------------------------------------------------------
--
/*
retorna filhos, do referente die na mes e ano imdicados
*/
CREATE OR REPLACE FUNCTION ga.get_filhos(codigo_die INTEGER,mes INTEGER,ano INTEGER) 
RETURNS TABLE(codigo_die int,nivel int) AS
$BODY$
	SELECT	ga.arvore_die.codigo_die,
		ga.arvore_die.nivel
	FROM	ga.die die INNER JOIN ga.arvore_die ON die.codigo_distribuidor = ga.arvore_die.codigo_die
	WHERE 	die.codigo_patrocinador = $1 and 
		ga.arvore_die.mes=$2 and 
		ga.arvore_die.ano=$3
       ORDER BY ga.arvore_die.nivel;
$BODY$
LANGUAGE sql;

--
-----------------------------------------------------------------------------------------------------------------
--
/*
retorna todo a linhagem dos ancestrais para uma determinada arvore do mes e ano referenciado para o die em quetão
observação a primeira linha se refere ao proprio DIE.
*/
CREATE OR REPLACE FUNCTION ga.get_ancestrais(codigo_die INTEGER, mes INTEGER, ano INTEGER)
RETURNS TABLE(codigo_distribuidor int, codigo_patrocinador int, mes int, ano int) AS
$BODY$
	with recursive get_pais(codigo_distribuidor,codigo_patrocinador,mes,ano) as --funcao recursiva para iterar sobre os pais
	(
		SELECT 	die.codigo_distribuidor, die.codigo_patrocinador, pontuacao.mes, pontuacao.ano FROM 
			ga.die die INNER JOIN ga.pontuacao_die pontuacao ON die.codigo_distribuidor = pontuacao.codigo_die

		WHERE 	die.codigo_distribuidor = $1 and 
			pontuacao.mes=$2 and 
			pontuacao.ano=$3

		UNION ALL
		
		SELECT 	die.codigo_distribuidor, die.codigo_patrocinador, get_pais.mes, get_pais.ano FROM 
			ga.die die INNER JOIN get_pais ON (die.codigo_distribuidor = get_pais.codigo_patrocinador)
				   INNER JOIN ga.pontuacao_die pontuacao ON die.codigo_distribuidor = pontuacao.codigo_die

		WHERE	get_pais.codigo_distribuidor <> get_pais.codigo_patrocinador and --EVITAR CICLO, NO CASO DA RAIZ 
			((pontuacao.mes=$2 and pontuacao.ano=$3) or (get_pais.mes=$2 and get_pais.ano=$3))
	)
	select * from get_pais;
$BODY$
LANGUAGE sql;

--
-----------------------------------------------------------------------------------------------------------------
--
CREATE OR REPLACE FUNCTION ga.calcular_nivel(_codigo_die INTEGER, _mes INTEGER, _ano INTEGER) --algoritmo para calcula o nivel do no com base nos niveis dos seus filhos
RETURNS VOID AS $$
DECLARE 
	_qtd_filho INTEGER[] := '{0,0,0,0,0,0,0,0}'::integer[];
	_nivel INTEGER := 0;
	_soma INTEGER := 0;
	filho RECORD;
BEGIN
	FOR filho IN	SELECT nivel, count(codigo_die) as qtd_filho --itera sobre os filhos do no
			FROM ga.get_filhos($1,$2,$3)
			WHERE codigo_die <> $1
			GROUP BY nivel
			ORDER BY nivel DESC
	LOOP			
		_qtd_filho[filho.nivel+1] := filho.qtd_filho; --atualiza a quantidade de filhos array inicia posição 1 por isso o +1			
	END LOOP;
	
	FOR i IN REVERSE 8..1 LOOP
		_soma := _soma + _qtd_filho[i];
		IF _soma >= 7 THEN
			_nivel := i;
			IF i>7 THEN
				_nivel := 7;
			END IF;
			EXIT;
		END IF;
	END LOOP;
	UPDATE ga.arvore_die SET nivel = _nivel, qtd_filhos = _qtd_filho
	WHERE codigo_die = $1 and mes = $2 and ano = $3;	
	IF found THEN		
		RETURN;
	END IF;	
	INSERT INTO ga.arvore_die(codigo_die, mes, ano, nivel, qtd_filhos)
	VALUES ($1, $2, $3, _nivel, _qtd_filho);
END
$$ LANGUAGE plpgsql;


--
-----------------------------------------------------------------------------------------------------------------
--
CREATE OR REPLACE FUNCTION ga.mantedor_arvore() --função que atualiza a arvore e mantem sua concistencia
RETURNS TRIGGER AS $$ 
DECLARE 
	_no RECORD;	
BEGIN
	
	FOR _no IN SELECT * from ga.get_ancestrais(new.codigo_die,new.mes,new.ano) LOOP			
		EXECUTE ga.calcular_nivel(_no.codigo_distribuidor,_no.mes,_no.ano);
	END LOOP;
	RETURN new;
END;
$$ LANGUAGE plpgsql;

--
-----------------------------------------------------------------------------------------------------------------
--

CREATE TRIGGER tgInsertPontuacao AFTER INSERT --gatilhos para as ações execultadas sobre a arvore
ON ga.pontuacao_die FOR EACH ROW
EXECUTE PROCEDURE ga.mantedor_arvore();